﻿# region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
# endregion

# region Using ArcGIS
using ESRI.ArcGIS.Controls;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.esriSystem;//不用从引用加入
using ESRI.ArcGIS.Carto;//管理图层
using ESRI.ArcGIS.CartoUI;
using ESRI.ArcGIS.DataSourcesGDB;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.Display;
# endregion


namespace popGis
{

    //成员及公有方法
    public partial class AccessDataConnectingClass
    {
        # region 成员

        private IWorkspaceFactory pWorkspaceFactory;
        private IFeatureWorkspace pWorkspace;
        private DataSet m_DataSet = new DataSet();

        //个人数据库文件名
        private string mdbFileName;
        //用Access方式联接          （此字符串可扩展为带有添加账户加密功能）
        private string connectingString;

        public DataTable DTableOfBasicInfo;   //基本信息表
        public DataTable DTableOfPopChange;   //人口变迁表
        public DataTable DTableOfShapeInfo;   //地理信息表
        public DataTable DTableOfSexStruct;   //性别结构表
        public DataTable DTableOfAgeStruct;   //年龄结构表
        public DataTable DTableOfPopIndustry; //行业人口表
        public ITable DB_ITable; //与几何要素集关联的表
        private int Index;   //记录表在数据集中的序号

        # endregion

        # region 属性

        public string AccessFileName
        {
            get { return mdbFileName; }
            set { mdbFileName = value; }
        }
        public string ConnectingString
        {
            get { return connectingString; }
            set { connectingString = value; }
        }

        # endregion

        # region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public AccessDataConnectingClass(/*string mdbfileName*/)
        {
            mdbFileName
                = @"E:\09343081\GIS_project\popGis\PopDB.mdb";

            connectingString
                = @"provider=Microsoft.JET.OLEDB.4.0;data source=" + mdbFileName;

            Index = 0;
        }
        # endregion

        # region 创建表DataTable方法

        /// <summary>
        /// 创建基本信息表
        /// </summary>
        public void CreateDTableOfBasicInfo()
        {
            string commandString = @"SELECT * FROM 基本信息表";//use like sql
            OleDbDataAdapter dbAdapater = new OleDbDataAdapter(commandString, connectingString);
            dbAdapater.Fill(m_DataSet);
            DTableOfBasicInfo = m_DataSet.Tables[Index++];
            DTableOfBasicInfo.TableName = "基本信息表";
        }

        /// <summary>
        /// 创建人口变动表
        /// </summary>
        public void CreateDTableOfPopChange()
        {
            string commandString = @"SELECT * FROM 人口变动表";
            OleDbDataAdapter dbAdapater = new OleDbDataAdapter(commandString, connectingString);
            dbAdapater.Fill(m_DataSet);
            DTableOfPopChange = m_DataSet.Tables[Index++];
            DTableOfPopChange.TableName = "人口变动表";
        }

        /// <summary>
        /// 创建空间信息表
        /// </summary>
        public void CreateDTableOfShapeInfo()
        {
            //string commandString = @"SELECT * FROM 空间信息表";
            //OleDbDataAdapter dbAdapater = new OleDbDataAdapter(commandString, connectingString);
            //dbAdapater.Fill(m_DataSet);
            //DTableOfShapeInfo = m_DataSet.Tables[Index++];
            //DTableOfShapeInfo.TableName = "空间信息表";

            if (DB_ITable == null) CreateITable();

            DataTable myTable = new DataTable();
            DataRow myNewRow; myNewRow = myTable.NewRow();

            DataColumn dt0 = new DataColumn("地名", typeof(string));
            DataColumn dt1 = new DataColumn("邮政编码", typeof(long));
            DataColumn dt2 = new DataColumn("重心X", typeof(Double));
            DataColumn dt3 = new DataColumn("重心Y", typeof(Double));
            DataColumn dt4 = new DataColumn("土地面积", typeof(Double));


            myTable.Columns.Add(dt0); myTable.Columns.Add(dt1);
            myTable.Columns.Add(dt2); myTable.Columns.Add(dt3); myTable.Columns.Add(dt4);

            IArea pArea = new PolygonClass(); IPoint tmpPoint; double area; ICursor pCursor = DB_ITable.Search(null, false);

            this.OpenWorspace();
            IFeatureClass pFeatureClass = pWorkspace.OpenFeatureClass("gz");
            IFeatureCursor pFeatureCursor = pFeatureClass.Search(null, false);
            IFeature pFeature = pFeatureCursor.NextFeature();
            while (pFeature != null)
            {
                //获得重心位置
                pArea = pFeature.Shape as IArea;
                tmpPoint = pArea.Centroid;
                //获得图形面积
                area = pArea.Area;
                //获得行政区名称
                IRow Row = (IRow)pFeature; int nameindex = Row.Fields.FindField("NAME");
                string regionName = Row.get_Value(nameindex).ToString();

                myNewRow["地名"] = regionName;
                myNewRow["邮政编码"] = getZipCode(regionName);
                myNewRow["重心X"] = tmpPoint.X;
                myNewRow["重心Y"] = tmpPoint.Y;
                myNewRow["土地面积"] = area;

                myTable.Rows.Add(myNewRow);
                myNewRow = myTable.NewRow();
                pFeature = pFeatureCursor.NextFeature();
            }

            DTableOfShapeInfo = myTable;//添加一个方法获得名字与编码即可
            DTableOfShapeInfo.TableName = "空间信息表";
        }

        /// <summary>
        /// 创建性别结构表
        /// </summary>
        public void CreateDTableOfSexStruct()
        {
            string commandString = @"SELECT * FROM 性别结构表";
            OleDbDataAdapter dbAdapater = new OleDbDataAdapter(commandString, connectingString);
            dbAdapater.Fill(m_DataSet);
            DTableOfSexStruct = m_DataSet.Tables[Index++];
            DTableOfSexStruct.TableName = "性别结构表";
        }

        /// <summary>
        /// 创建年龄结构表
        /// </summary>
        public void CreateDTableOfAgeStruct()
        {
            string commandString = @"SELECT * FROM 年龄结构表";
            OleDbDataAdapter dbAdapater = new OleDbDataAdapter(commandString, connectingString);
            dbAdapater.Fill(m_DataSet);
            DTableOfAgeStruct = m_DataSet.Tables[Index++];
            DTableOfAgeStruct.TableName = "年龄结构表";
        }

        /// <summary>
        /// 创建行业人口表
        /// </summary>
        public void CreateDTableOfPopIndustry()
        {
            string commandString = @"SELECT * FROM 行业人口表";
            OleDbDataAdapter dbAdapater = new OleDbDataAdapter(commandString, connectingString);
            dbAdapater.Fill(m_DataSet);
            DTableOfPopIndustry = m_DataSet.Tables[Index++];
            DTableOfPopIndustry.TableName = "行业人口表";
        }

        /// <summary>
        /// 创建几何要素表ITable
        /// </summary>
        public void CreateITable()
        {
            this.OpenWorspace();
            DB_ITable = pWorkspace.OpenTable("gz");
        }


        # region FUNCTIONS

        /// <summary>
        /// 将属性表转化为DataTable结构，
        /// 以显示在DataGridView中
        /// </summary>
        /// <returns></returns>
        public DataTable CreateDTableFromITable()
        {
            if (DB_ITable == null) CreateITable();

            DataTable DTableFromITable = new DataTable();

            try
            {
                DTableFromITable = CreateDataTableFromITable(DB_ITable, "图形属性表");
            }
            catch
            {
                MessageBox.Show("无法从属性表创建数据表对象。", "错误：");
            }
            return DTableFromITable;
        }

        /// <summary>
        /// 打开地理数据工作空间
        /// </summary>
        /// 
        private void OpenWorspace()
        {
            if (pWorkspaceFactory == null)
            {
                pWorkspaceFactory = new AccessWorkspaceFactoryClass();
                pWorkspace = pWorkspaceFactory.OpenFromFile(mdbFileName, 0) as IFeatureWorkspace;
            }
        }

        # endregion

        # endregion
    }


    //部分方法
    public partial class AccessDataConnectingClass
    {
        
        # region 有用的函数


        /// <summary>
        /// 从地名获得邮政编码，but，最好能将编码表存于程序外面，便于更新
        /// </summary>
        /// <param name="RegionName">全名，应>2字符</param>
        /// <returns></returns>
        /// 
        public long getZipCode(string RegionName)
        {
            string left2 = RegionName.Substring(0, 2);
            switch (left2)
            {
                case "广州": return 510000;
                case "番禺": return 511400;
                case "花都": return 510800;
                case "从化": return 510900;
                case "增城": return 511300;
                case "汕尾": return 516600;
                case "海丰": return 516400;
                case "陆河": return 516700;
                case "陆丰": return 516500;
                case "潮阳": return 515100;
                case "阳江": return 529500;
                case "阳春": return 529600;
                case "阳东": return 529500;
                case "阳西": return 529800;
                case "揭阳": return 515500;
                case "揭东": return 515500;
                case "惠来": return 515200;
                case "揭西": return 515400;
                case "普宁": return 515300;
                case "茂名": return 525000;
                case "化州": return 525100;
                case "高州": return 525200;
                case "信宜": return 525300;
                case "电白": return 525400;
                case "江门": return 529000;
                case "台山": return 529200;
                case "开平": return 529300;
                case "恩平": return 529400;
                case "新会": return 529100;
                case "鹤山": return 529700;
                case "韶关": return 512000;
                case "曲江": return 512100;
                case "翁源": return 512600;
                case "新丰": return 511100;
                case "始兴": return 512500;
                case "仁化": return 512300;
                case "南雄": return 512400;
                case "乳源": return 512700;
                case "乐昌": return 512200;
                case "惠州": return 516000;
                case "惠东": return 526300;
                case "惠阳": return 516200;
                case "博罗": return 516100;
                case "龙门": return 511200;
                case "梅州": return 514000;
                case "梅县": return 514000;
                case "蕉岭": return 514100;
                case "大埔": return 514200;
                case "丰顺": return 514300;
                case "五华": return 514400;
                case "兴宁": return 514500;
                case "平远": return 514600;
                case "汕头": return 515000;
                case "澄海": return 515800;
                case "南澳": return 515900;
                case "深圳": return 518000;
                case "宝安": return 518100;
                case "珠海": return 519000;
                case "斗门": return 519100;
                case "佛山": return 528000;
                case "南海": return 528200;
                case "高明": return 528500;
                case "三水": return 528100;
                case "肇庆": return 526000;
                case "高要": return 526100;
                case "四会": return 526200;
                case "怀集": return 526400;
                case "封开": return 526500;
                case "德庆": return 526600;
                case "广宁": return 526300;
                case "湛江": return 524000;
                case "遂溪": return 524300;
                case "廉江": return 524400;
                case "吴川": return 524500;
                case "徐闻": return 524100;
                case "雷州": return 524200;
                case "中山": return 528400;
                case "河源": return 517000;
                case "连平": return 517100;
                case "和平": return 517200;
                case "龙川": return 517300;
                case "紫金": return 517400;
                case "清远": return 511500;
                case "佛冈": return 511600;
                case "英德": return 513000;
                case "连山": return 513200;
                case "阳山": return 513100;
                case "连南": return 513300;
                case "连州": return 513400;
                case "顺德": return 528300;
                case "云浮": return 527300;
                case "罗定": return 527200;
                case "郁南": return 527100;
                case "新兴": return 527400;
                case "潮州": return 515600;
                case "潮安": return 515600;
                case "饶平": return 515700;
                case "东莞": return 511700;
                default:
                    return 999999;
            }
        }


        /// <summary>
        /// 判断是否创建好某类指标的数据表，参数可选：
        /// [基本信息表/人口变动表/空间信息表/性别结构表/年龄结构表/行业人口表]
        /// </summary>
        /// <param name="dTableName">数据表类型</param>
        /// <returns></returns>
        /// 
        public bool isDataTableNull(string dTableName)
        {
            return !(m_DataSet.Tables.Contains(dTableName));
        }

        # endregion

        # region 创建属性表的DataTable格式
        /// <summary>
        /// 从属性表pTable创建内容对应且名为tableName的DataTable
        /// </summary>
        /// <param name="pTable"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// 
        static public DataTable CreateDataTableFromITable(ITable pTable, string tableName)
        {            
            DataTable pDataTable = CreateDataTableByITable(pTable, tableName); //创建空DataTable            
            DataRow pDataRow = null;//创建DataTable的行对象 
            ICursor pCursor = pTable.Search(null, false);int n = 0;            
            IRow pRow = pCursor.NextRow();//取得ITable中的行信息             
            while (pRow != null)
            {                 
                pDataRow = pDataTable.NewRow();//新建DataTable的行对象
                for (int i = 0; i < pRow.Fields.FieldCount; i++)
                {
                    //如果字段类型为esriFieldTypeGeometry，则根据图层类型设置字段值 
                    if (pRow.Fields.get_Field(i).Type == esriFieldType.esriFieldTypeGeometry) {
                        pDataRow[i] = "Polygon";
                    }
                    //当图层类型为Anotation时，要素类中会有esriFieldTypeBlob类型的数据， 
                    //其存储的是标注内容，如此情况需将对应的字段值设置为Element 
                    else if (pRow.Fields.get_Field(i).Type == esriFieldType.esriFieldTypeBlob){
                        pDataRow[i] = "Element";
                    }    else   {
                        pDataRow[i] = pRow.get_Value(i);
                    }
                }                
                pDataTable.Rows.Add(pDataRow);//添加DataRow到DataTable 
                pDataRow = null;
                n++;//为保证效率，一次只装载最多条记录                 
                if (n == 2000) {
                    pRow = null;
                }   else       {
                    pRow = pCursor.NextRow();
                }
            }
            return pDataTable;
        }


        /// <summary>
        /// 从属性表pTable创建字段对应且名为tableName的空DataTable
        /// </summary>
        /// <param name="pTable"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// 
        static private DataTable CreateDataTableByITable(ITable pTable, string tableName)
        {
            //创建一个DataTable表 
            DataTable pDataTable = new DataTable(tableName);

            IField pField = null;
            DataColumn pDataColumn;
            //根据每个字段的属性建立DataColumn对象 
            for (int i = 0; i < pTable.Fields.FieldCount; i++)
            {
                pField = pTable.Fields.get_Field(i);
                //新建一个DataColumn并设置其属性 
                pDataColumn = new DataColumn(pField.Name);
                if (pField.Name == pTable.OIDFieldName)
                {
                    pDataColumn.Unique = true;//字段值是否唯一 
                }
                //字段值是否允许为空 
                pDataColumn.AllowDBNull = pField.IsNullable;
                //字段别名 
                pDataColumn.Caption = pField.AliasName;
                //字段数据类型 
                pDataColumn.DataType = System.Type.GetType(ParseFieldType(pField.Type));
                //字段默认值 
                pDataColumn.DefaultValue = pField.DefaultValue;
                //当字段为String类型是设置字段长度 
                if (pField.VarType == 8)
                {
                    pDataColumn.MaxLength = pField.Length;
                }
                //字段添加到表中 
                pDataTable.Columns.Add(pDataColumn);
                pField = null;
                pDataColumn = null;
            }
            return pDataTable;
        }


        # endregion

        # region 字段类型转换

        /// <summary>
        /// 将GeoDatabase字段类型转换成.Net相应的数据类型
        /// </summary>
        /// <param name="fieldType">地理数据字段类型</param>
        /// <returns>.Net数据类型</returns>
        /// 
        static private string ParseFieldType(esriFieldType fieldType)
        {
            switch (fieldType)
            {
                case esriFieldType.esriFieldTypeBlob:
                    return "System.String";
                case esriFieldType.esriFieldTypeDate:
                    return "System.DateTime";
                case esriFieldType.esriFieldTypeDouble:
                    return "System.Double";
                case esriFieldType.esriFieldTypeGeometry:
                    return "System.String";
                case esriFieldType.esriFieldTypeGlobalID:
                    return "System.String";
                case esriFieldType.esriFieldTypeGUID:
                    return "System.String";
                case esriFieldType.esriFieldTypeInteger:
                    return "System.Int32";
                case esriFieldType.esriFieldTypeOID:
                    return "System.String";
                case esriFieldType.esriFieldTypeRaster:
                    return "System.String";
                case esriFieldType.esriFieldTypeSingle:
                    return "System.Single";
                case esriFieldType.esriFieldTypeSmallInteger:
                    return "System.Int32";
                case esriFieldType.esriFieldTypeString:
                    return "System.String";
                default:
                    return "System.String";
            }
        }


        # endregion



        # region TestAccessDataConnectingClass
        //myDataset.OpenWorspace();
        //myDataset.CreateITable();

        //AccessDataConnectingClass myDataset = new AccessDataConnectingClass();

        //myDataset.CreateDTableOfBasicInfo();
        //myDataset.CreateDTableOfAgeStruct();
        //myDataset.CreateDTableOfPopChange();
        //myDataset.CreateDTableOfSexStruct();
        //myDataset.CreateDTableOfShapeInfo();
        //myDataset.CreateDTableOfPopIndustry();

        //DataTable table = myDataset.DTableOfBasicInfo;
        //DataTable table = myDataset.DTableOfAgeStruct;
        //DataTable table = myDataset.DTableOfPopChange;
        //DataTable table = myDataset.DTableOfSexStruct;
        //DataTable table = myDataset.DTableOfShapeInfo;
        //DataTable table = myDataset.DTableOfPopIndustry;

        //DataGridForm frm = new DataGridForm(table);frm.Show();

        //bool b1 = myDataset.isDataTableNull("基本信息表");
        //bool b2 = myDataset.isDataTableNull("年龄结构表");
        //bool b3 = myDataset.isDataTableNull("人口变迁表");
        # endregion
    }

}
